<?php
class ModelCatalogReview extends Model {		
	public function addReview($product_id, $data) {
		$this->db->query("INSERT INTO " . DB_PREFIX . "review SET author = '" . $this->db->escape($data['name']) . 
			"', customer_id = '" . (int)$this->customer->getId() . 
			"', product_id = '" . (int)$product_id . 
			"', text = '" . $this->db->escape($data['text']) . 
			"', rating = '" . (int)$data['rating'] . 
			"', date_added = NOW(), lang_id = '" . (int)$this->config->get('config_language_id') . "'");
	}
		
	public function getReviewsByProductId($product_id, $start = 0, $limit = 20) {
		$db_result = $this->cache->get('getReviewsByProductId.' . $product_id . '.' . $start . '.' . $limit);
		if (!$db_result) {
			$query = $this->db->query("SELECT r.review_id, r.author, r.rating, r.date_added, r.text FROM " . 
				DB_PREFIX . "review r WHERE r.product_id = '" . (int)$product_id . 
				"' AND r.status = '1' ORDER BY r.date_added DESC LIMIT " . (int)$start . "," . (int)$limit);
			$db_result = $query->rows;
			$this->cache->set('getReviewsByProductId.' . $product_id . '.' . $start . '.' . $limit, $db_result);
		}			 	 		
		return $db_result;
	}
	
	public function getAverageRating($product_id) {
		$query = $this->db->query("SELECT AVG(rating) AS total FROM " . 
			DB_PREFIX . "review WHERE status = '1' AND product_id = '" . (int)$product_id . "' GROUP BY product_id");
		
		if (isset($query->row['total'])) {
			return (int)$query->row['total'];
		} else {
			return 0;
		}
	}	
	
	public function getTotalReviews() {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r WHERE r.status = '1'");
		
		return $query->row['total'];
	}

	public function getTotalReviewsByProductId($product_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r WHERE r.product_id = '" . 
			(int)$product_id . "' AND r.status = '1'");
		
		return $query->row['total'];
	}
	
	public function getReviewsForHome($limit = 3) {
		$sql = "SELECT r.`author`, r.`text`, r.`rating`, r.`date_added`, p.`product_id`, p.`image`, pd.`name` FROM " . 
			DB_PREFIX . "review r LEFT JOIN " . DB_PREFIX . "product p ON r.`product_id` = p.`product_id` LEFT JOIN " . 
			DB_PREFIX . "product_description pd ON p.`product_id` = pd.`product_id` WHERE r.status = '1' AND pd.`language_id` = '" . 
			(int)$this->config->get('config_language_id') . "' ORDER BY r.`date_added` DESC LIMIT " . $limit;
		
		$query = $this->db->query($sql);
		
		return $query->rows;
	}
	
	public function getGroupRating($product_id) {
		$reviewsrating = array();
		$reviewsrating[5] = 0;
		$reviewsrating[4] = 0;
		$reviewsrating[3] = 0;
		$reviewsrating[2] = 0;
		$reviewsrating[1] = 0;
		
		$query = $this->db->query("SELECT rating, COUNT(rating) AS total FROM " . DB_PREFIX . 
			"review WHERE status = '1' AND product_id = '" . (int)$product_id . "' GROUP BY rating ORDER BY rating DESC");
		$result = $query->rows;
		
		if($result){
			foreach ($result as $item){
				$reviewsrating[$item['rating']] = $item['total'];
			}
		}
		return $reviewsrating;
	}
	
	public function getReviewsByCategoryId($categoryId = 0, $start = 0, $limit = 20) {
		$db_result = $this->cache->get('getReviewsByCategoryId.' . $categoryId . '.' . $start . '.' . $limit);
		if (!$db_result) {
			if($categoryId){
				$categoryIds = $categoryId;
				if ($categoryIds) {
					$this->load->model('catalog/category');
					$categories = $this->model_catalog_category->getCategoriesByParentId($categoryIds);
					foreach ($categories as $category_id) {
						$categoryIds .= "," . $category_id;
					}
				}
				
				$sql = "SELECT r.`author`, r.`text`, r.`rating`, r.`date_added`, p.`product_id`, p.`image`, pd.`name` FROM " . 
					DB_PREFIX . "review r JOIN " . DB_PREFIX . "product p ON r.`product_id` = p.`product_id` JOIN " . 
					DB_PREFIX . "product_description pd ON (p.`product_id` = pd.`product_id` AND pd.`language_id` = '" . (int)$this->config->get('config_language_id') . "') JOIN " . 
					DB_PREFIX . "product_to_category pc on pd.product_id = pc.product_id WHERE r.status = '1' AND pc.category_id IN(" . 
					$categoryIds . ") GROUP BY r.review_id ORDER BY r.`date_added` DESC LIMIT " . $limit;
			}else{
				$sql = "SELECT r.`author`, r.`text`, r.`rating`, r.`date_added`, p.`product_id`, p.`image`, pd.`name` FROM " . 
					DB_PREFIX . "review r JOIN " . DB_PREFIX . "product p ON r.`product_id` = p.`product_id` JOIN " . 
					DB_PREFIX . "product_description pd ON (p.`product_id` = pd.`product_id` AND pd.`language_id` = '" . (int)$this->config->get('config_language_id') . "') " . 
					"WHERE r.status = '1' ORDER BY r.`date_added` DESC LIMIT " . $limit;
			}
			$query = $this->db->query($sql);
			$db_result = $query->rows;
			$this->cache->set('getReviewsByCategoryId.' . $categoryId . '.' . $start . '.' . $limit, $db_result);
		}			 	 		
		return $db_result;
	}
	
	public function getTotalReviewsByCategoryId($categoryId = 0) {
		$db_result = $this->cache->get('getTotalReviewsByCategoryId.' . $categoryId);
		if (!$db_result) {
			if($categoryId > 0){
				$categoryIds = $categoryId;
				if ($categoryIds) {
					$this->load->model('catalog/category');
					$categories = $this->model_catalog_category->getCategoriesByParentId($categoryIds);
					foreach ($categories as $category_id) {
						$categoryIds .= "," . $category_id;
					}
				}
				
				$query = $this->db->query("SELECT COUNT(DISTINCT r.review_id) AS total FROM " . DB_PREFIX . "review r JOIN " . 
					DB_PREFIX . "product_to_category pc ON (r.product_id = pc.product_id) WHERE pc.category_id IN(" . $categoryIds . 
					") AND r.status = '1'");
			}else{
				$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r WHERE r.status = '1'");
			}	
			$db_result = $query->row['total'];
			$this->cache->set('getTotalReviewsByCategoryId.' . $categoryId, $db_result);
		}			 	 		
		return $db_result;
	}
	
}
?>